/*     */ package com.newcrm.db;
/*     */ 
/*     */ import com.newcrm.exception.DBException;
/*     */ import com.newcrm.util.Util;
/*     */ import java.sql.Connection;
/*     */ import java.sql.SQLException;
/*     */ import java.util.ArrayList;
/*     */ import java.util.Calendar;
/*     */ import java.util.Date;
/*     */ import java.util.GregorianCalendar;
/*     */ import java.util.List;
/*     */ import java.util.Map;
/*     */ import org.apache.commons.dbutils.DbUtils;
/*     */ import org.apache.commons.dbutils.GenKeyQueryRunner;
/*     */ import org.apache.commons.dbutils.MyMapListHandler;
/*     */ import org.apache.commons.dbutils.QueryRunner;
/*     */ import org.apache.commons.dbutils.handlers.MapHandler;
/*     */ import org.apache.commons.dbutils.handlers.MapListHandler;
/*     */ import org.apache.commons.dbutils.handlers.ScalarHandler;
/*     */ import org.apache.log4j.Logger;
/*     */ 
/*     */ public class TaskManager
/*     */ {
/*  28 */   private static Logger logger = Logger.getLogger(TaskManager.class.getName());
/*     */ 
/*     */   public int add(String taskTitle, Date dueDate, String responsibleUserId, int categoryId, int taskVisible, String referenceUrl, String referenceTitle, int companyId, int referenceId, int referenceType, String userId)
/*     */     throws DBException
/*     */   {
/*  51 */     if ((taskTitle == null) || (taskTitle.trim().length() == 0)) {
/*  52 */       return 0;
/*     */     }
/*  54 */     if (companyId <= 0) {
/*  55 */       return 0;
/*     */     }
/*     */ 
/*  58 */     Connection conn = ConnectionManager.getInstance().getConnection();
/*  59 */     if (conn == null) {
/*  60 */       throw new DBException();
/*     */     }
/*     */ 
/*  63 */     GenKeyQueryRunner runner = new GenKeyQueryRunner(
/*  64 */       new ScalarHandler(), new String[] { "TaskId" });
/*     */     try {
/*  66 */       runner.update(conn, "INSERT INTO T_Task_Info (TaskTitle,DueDate,ResponsibleUserId,CategoryId,TaskVisible,ReferenceUrl,ReferenceTitle,CompanyId,CreateDate,CreateUserId,ReferenceId,ReferenceType,FinishFlag) VALUES (?,?,?,?,?,?,?,?,NOW(),?,?,?,0)", new Object[] { taskTitle, dueDate, 
/*  67 */         responsibleUserId, Integer.valueOf(categoryId), Integer.valueOf(taskVisible), referenceUrl, 
/*  68 */         referenceTitle, Integer.valueOf(companyId), userId, Integer.valueOf(referenceId), 
/*  69 */         Integer.valueOf(referenceType) });
/*  70 */       return ((Long)runner.getGeneratedKeys()).intValue();
/*     */     } catch (SQLException e) {
/*  72 */       e.printStackTrace();
/*  73 */       logger.warn("add task failed", e);
/*     */     } finally {
/*     */       try {
/*  76 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/*  78 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */ 
/*  82 */     return 0;
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getMyTasks(String userId)
/*     */     throws DBException, SQLException
/*     */   {
/*  95 */     Connection conn = ConnectionManager.getInstance().getConnection();
/*  96 */     if (conn == null) {
/*  97 */       return null;
/*     */     }
/*  99 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 102 */       return (List)runner.query(conn, "SELECT TaskId,TaskTitle,DueDate,CategoryId FROM T_Task_Info WHERE FinishFlag = 0 AND ResponsibleUserId = ? ORDER BY DueDate ASC", 
/* 103 */         new MapListHandler(), new Object[] { userId });
/*     */     }
/*     */     finally {
/*     */       try {
/* 107 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 109 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getList(int referenceId, int referenceType, int status, int category, String responsibleUserId, String groupIds, int companyId, int offset, int pageSize)
/*     */     throws DBException, SQLException
/*     */   {
/* 132 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 133 */     if (conn == null) {
/* 134 */       return null;
/*     */     }
/* 136 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 138 */       StringBuffer sql = new StringBuffer("SELECT * FROM T_Task_Info WHERE CompanyId = ? ");
/* 139 */       List params = new ArrayList();
/* 140 */       params.add(Integer.valueOf(companyId));
/*     */ 
/* 142 */       if ((status == 0) || (status == 1))
/*     */       {
/* 146 */         sql.append(" AND FinishFlag = ? ");
/* 147 */         params.add(Integer.valueOf(status));
/*     */ 
/* 150 */         if ((responsibleUserId != null) && 
/* 151 */           (responsibleUserId.trim().length() > 0)) {
/* 152 */           sql.append(" AND ResponsibleUserId = ? ");
/* 153 */           params.add(responsibleUserId);
/*     */         }
/* 156 */         else if ((groupIds != null) && (groupIds.trim().length() > 0)) {
/* 157 */           sql.append(" AND ResponsibleUserId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId IN (" + 
/* 158 */             groupIds + "))");
/* 159 */           params.add(Integer.valueOf(companyId));
/*     */         }
/*     */       }
/* 162 */       else if (status == 2) {
/* 163 */         sql.append(" AND ResponsibleUserId <> CreateUserId ");
/*     */ 
/* 166 */         if ((responsibleUserId != null) && 
/* 167 */           (responsibleUserId.trim().length() > 0)) {
/* 168 */           sql.append(" AND CreateUserId = ? ");
/* 169 */           params.add(responsibleUserId);
/*     */         }
/* 171 */         else if ((groupIds != null) && (groupIds.trim().length() > 0)) {
/* 172 */           sql.append(" AND CreateUserId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId IN (" + 
/* 173 */             groupIds + ")) ");
/* 174 */           params.add(Integer.valueOf(companyId));
/*     */         }
/*     */ 
/*     */       }
/*     */ 
/* 180 */       if (referenceId > 0) {
/* 181 */         sql.append(" AND ReferenceId = ? AND ReferenceType = ? ");
/* 182 */         params.add(Integer.valueOf(referenceId));
/* 183 */         params.add(Integer.valueOf(referenceType));
/*     */       }
/*     */ 
/* 186 */       if (category > 0) {
/* 187 */         sql.append(" AND CategoryId = ? ");
/* 188 */         params.add(Integer.valueOf(category));
/*     */       }
/*     */ 
/* 191 */       sql.append(" ORDER BY CASE WHEN DueDate IS NULL THEN 1 ELSE 0 END, DueDate ASC ");
/*     */ 
/* 194 */       if (offset >= 0) {
/* 195 */         sql.append(" LIMIT ").append(offset).append(", ")
/* 196 */           .append(pageSize);
/*     */       }
/* 198 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 199 */         params.toArray());
/*     */     } finally {
/*     */       try {
/* 202 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 204 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int delete(int taskId, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 220 */     if (taskId <= 0) {
/* 221 */       return 0;
/*     */     }
/* 223 */     if ((userId == null) || (userId.trim().length() == 0)) {
/* 224 */       return 0;
/*     */     }
/* 226 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 227 */     if (conn == null) {
/* 228 */       throw new DBException();
/*     */     }
/* 230 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 232 */       return runner.update(conn, "DELETE FROM T_Task_Info WHERE TaskId = ? AND (TaskVisible = 1 OR (TaskVisible = 0 AND (ResponsibleUserId = ? OR CreateUserId = ?)))", new Object[] { Integer.valueOf(taskId), userId, 
/* 233 */         userId });
/*     */     } finally {
/*     */       try {
/* 236 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 238 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateStatus(int taskId, int status, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 255 */     if (taskId <= 0) {
/* 256 */       return 0;
/*     */     }
/* 258 */     if ((userId == null) || (userId.trim().length() == 0)) {
/* 259 */       return 0;
/*     */     }
/* 261 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 262 */     if (conn == null) {
/* 263 */       throw new DBException();
/*     */     }
/* 265 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 267 */       int result = runner.update(conn, "UPDATE T_Task_Info SET FinishFlag = ?, UpdateDate = NOW() WHERE TaskId = ? AND (TaskVisible = 1 OR (TaskVisible = 0 AND (ResponsibleUserId = ? OR CreateUserId = ?)))", new Object[] { 
/* 268 */         Integer.valueOf(status), Integer.valueOf(taskId), userId, userId });
/* 269 */       if ((result > 0) && (status == 1))
/*     */       {
/* 274 */         List tasks = (List)runner.query(conn, 
/* 275 */           "SELECT ReferenceId, ReferenceType, ReferenceTitle, TaskTitle, CompanyId FROM T_Task_Info WHERE TaskId = ?", new MyMapListHandler(), new Object[] { Integer.valueOf(taskId) });
/* 276 */         if ((tasks != null) && (tasks.size() == 1)) {
/* 277 */           Object dealId = Integer.valueOf(-1);
/* 278 */           Object dealName = null;
/* 279 */           int referenceType = Util.str2Int(((Map)tasks.get(0)).get(
/* 280 */             "ReferenceType"));
/*     */ 
/* 282 */           if (referenceType == 2) {
/* 283 */             dealId = ((Map)tasks.get(0)).get("ReferenceId");
/* 284 */             dealName = ((Map)tasks.get(0)).get("ReferenceTitle");
/*     */           }
/* 286 */           runner.update(conn, "INSERT INTO T_Note_Info (NoteType,SubjectId,SubjectName,SubjectType,AttachFiles,NoteDate,NextContactDate,Remark,NoteVisible,PermissionsId,CompanyId,DealId,DealName,CreateUserId,UpdateUserId,CreateDate,UpdateDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())", new Object[] { 
/* 287 */             Integer.valueOf(3), 
/* 288 */             ((Map)tasks.get(0)).get("ReferenceId"), 
/* 289 */             ((Map)tasks.get(0)).get("ReferenceTitle"), Integer.valueOf(referenceType), 
/* 290 */             null, new Date(), null, 
/* 291 */             ((Map)tasks.get(0)).get("TaskTitle"), 
/* 292 */             Integer.valueOf(1), null, 
/* 293 */             ((Map)tasks.get(0)).get("CompanyId"), dealId, dealName, 
/* 294 */             userId, userId });
/*     */         }
/*     */       }
/*     */ 
/* 298 */       return result;
/*     */     } finally {
/*     */       try {
/* 301 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 303 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateDueDate(int taskId, Date newDueDate, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 320 */     if (taskId <= 0) {
/* 321 */       return 0;
/*     */     }
/* 323 */     if ((userId == null) || (userId.trim().length() == 0)) {
/* 324 */       return 0;
/*     */     }
/* 326 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 327 */     if (conn == null) {
/* 328 */       throw new DBException();
/*     */     }
/* 330 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 332 */       return runner.update(conn, "UPDATE T_Task_Info SET DueDate = ? WHERE TaskId = ? AND (TaskVisible = 1 OR (TaskVisible = 0 AND (ResponsibleUserId = ? OR CreateUserId = ?)))", new Object[] { 
/* 333 */         newDueDate, Integer.valueOf(taskId), userId, userId });
/*     */     } finally {
/*     */       try {
/* 336 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 338 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int update(int taskId, String taskTitle, Date dueDate, String responsibleUserId, int categoryId, int taskVisible, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 360 */     if (taskId <= 0) {
/* 361 */       return 0;
/*     */     }
/* 363 */     if ((userId == null) || (userId.trim().length() == 0)) {
/* 364 */       return 0;
/*     */     }
/* 366 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 367 */     if (conn == null) {
/* 368 */       throw new DBException();
/*     */     }
/* 370 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 372 */       return runner.update(conn, "UPDATE T_Task_Info SET TaskTitle = ?, DueDate = ?, ResponsibleUserId = ?, CategoryId = ?, TaskVisible = ? WHERE TaskId = ? AND (TaskVisible = 1 OR (TaskVisible = 0 AND (ResponsibleUserId = ? OR CreateUserId = ?)))", new Object[] { taskTitle, 
/* 373 */         dueDate, responsibleUserId, Integer.valueOf(categoryId), Integer.valueOf(taskVisible), 
/* 374 */         Integer.valueOf(taskId), userId, userId });
/*     */     } finally {
/*     */       try {
/* 377 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 379 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int getNotFinishTaskNum(String userId, int type)
/*     */   {
/* 393 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 394 */     if (conn == null) {
/* 395 */       return 0;
/*     */     }
/*     */ 
/* 398 */     List params = new ArrayList();
/* 399 */     params.add(userId);
/* 400 */     StringBuffer sql = new StringBuffer(
/* 401 */       "SELECT COUNT(*) FROM T_Task_Info WHERE FinishFlag = 0 AND ResponsibleUserId = ? ");
/*     */     int end;
/* 403 */     if (type == -1)
/*     */     {
/* 405 */       sql.append(" AND DATEDIFF(DueDate, NOW()) < 0");
/* 406 */     } else if (type == 1)
/*     */     {
/* 408 */       sql.append(" AND DATEDIFF(DueDate, NOW()) = 0");
/* 409 */     } else if (type == 2)
/*     */     {
/* 411 */       sql.append(" AND DATEDIFF(DueDate, NOW()) = 1");
/* 412 */     } else if (type == 3)
/*     */     {
/* 414 */       Calendar calendar = new GregorianCalendar();
/* 415 */       calendar.setTime(new Date());
/* 416 */       calendar.setFirstDayOfWeek(2);
/* 417 */       int weekday = calendar.get(7) - 1;
/* 418 */       if (weekday == 0) {
/* 419 */         weekday = 7;
/*     */       }
/* 421 */       int start = weekday * -1 + 1;
/* 422 */       end = 7 - weekday;
/* 423 */       sql.append(" AND DATEDIFF(DueDate, NOW()) >= ? AND DATEDIFF(DueDate, NOW()) <= ? ");
/* 424 */       params.add(Integer.valueOf(start));
/* 425 */       params.add(Integer.valueOf(end));
/*     */     }
/*     */     else {
/* 428 */       Calendar calendar = new GregorianCalendar();
/* 429 */       calendar.setTime(new Date());
/* 430 */       calendar.setFirstDayOfWeek(2);
/* 431 */       int weekday = calendar.get(7) - 1;
/* 432 */       if (weekday == 0) {
/* 433 */         weekday = 7;
/*     */       }
/* 435 */       int start = 7 - weekday + 1;
/* 436 */       end = start + 6;
/* 437 */       sql.append(" AND DATEDIFF(DueDate, NOW()) >= ? AND DATEDIFF(DueDate, NOW()) <= ? ");
/* 438 */       params.add(Integer.valueOf(start));
/* 439 */       params.add(Integer.valueOf(end));
/*     */     }
/*     */ 
/* 442 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 444 */       Object obj = runner.query(conn, sql.toString(), 
/* 445 */         new ScalarHandler(1), params.toArray());
/* 446 */       if (!(obj instanceof Long)) {
/* 447 */         return 0;
/*     */       }
/* 449 */       return ((Long)obj).intValue();
/*     */     } catch (SQLException e) {
/* 451 */       logger.warn("get count failed", e);
/*     */     } finally {
/*     */       try {
/* 454 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 456 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 459 */     return 0;
/*     */   }
/*     */ 
/*     */   public Map<String, Object> getTaskDetail(int taskId)
/*     */     throws DBException, SQLException
/*     */   {
/* 472 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 473 */     if (conn == null) {
/* 474 */       throw new DBException();
/*     */     }
/*     */ 
/* 477 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 480 */       return (Map)runner.query(conn, "SELECT TaskTitle, DueDate, ReferenceId, ResponsibleUserId, CategoryId, TaskVisible FROM T_Task_Info WHERE TaskId = ?", new MapHandler(), new Object[] { 
/* 481 */         Integer.valueOf(taskId) });
/*     */     } finally {
/*     */       try {
/* 484 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 486 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getTotalNumInfo(int companyId, String responsibleUserId, Date startDate, Date endDate)
/*     */   {
/* 502 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 503 */     if (conn == null) {
/* 504 */       return null;
/*     */     }
/* 506 */     StringBuffer sql = new StringBuffer(
/* 507 */       "SELECT count(*) AS totalNum, CategoryId FROM T_Task_Info WHERE companyId = ? ");
/* 508 */     List params = new ArrayList();
/* 509 */     params.add(Integer.valueOf(companyId));
/*     */ 
/* 511 */     if ((responsibleUserId != null) && (responsibleUserId.trim().length() > 0)) {
/* 512 */       sql.append(" AND ResponsibleUserId = ? ");
/* 513 */       params.add(responsibleUserId);
/*     */     }
/*     */ 
/* 516 */     if (startDate != null) {
/* 517 */       sql.append(" AND DATEDIFF(CreateDate, ?) >= 0 ");
/* 518 */       params.add(startDate);
/*     */     }
/* 520 */     if (endDate != null) {
/* 521 */       sql.append(" AND DATEDIFF(CreateDate, ?) <= 0 ");
/* 522 */       params.add(endDate);
/*     */     }
/*     */ 
/* 525 */     sql.append(" GROUP BY CategoryId");
/* 526 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 528 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 529 */         params.toArray());
/*     */     } catch (SQLException e) {
/* 531 */       logger.warn("get count failed", e);
/*     */     } finally {
/*     */       try {
/* 534 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 536 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 539 */     return null;
/*     */   }
/*     */ }

/* Location:           C:\E\referenceProject\CRM参考\qikebao\WEB-INF\lib\qikebao.jar
 * Qualified Name:     com.newcrm.db.TaskManager
 * JD-Core Version:    0.6.1
 */